﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL
{
    public class ClientEntity
    {
        public Int32 Id { get; set; }

        public String Name { get; set; }
        public String Job { get; set; }
        public String Address { get; set; }
        public String Phone { get; set; }
        public String Akhesaye { get; set; }
        public Int32 Round { get; set; }


        /*
 
            Class Client which contains all methods related to Customer table
 
        */

    }
    class Client
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";





        public static ClientEntity GetClient(int id)
        {
            ClientEntity c = null;
            cmdText = "select * from Customer where Customer_ID = @Customer_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_ID", id);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return c;
        }


        /*   
           public static ClientEntity GetClient(String pid)
           {
             ClientEntity c = null;


               cmdText = "select * from Customer where Customer_ID=?";
               SqlConnection con = new SqlConnection(conString);
               SqlCommand cmd = new SqlCommand(cmdText, con);
               cmd.Parameters.AddWithValue("@Customer_ID", pid);
               try
               {
                   con.Open();

                   SqlDataReader reader = cmd.ExecuteReader();
                   if(reader.Read())
                   {
                       c = fillEntityFromReader(reader);
                   }
                
               }
               catch (Exception ex)
               {
                   MessageBox.Show("Exception==>>" + ex.Message);
               }

               finally
               {
                   if(con.State == ConnectionState.Open)
                       con.Close();
               }

               return c;

           }

   */

        public static List<ClientEntity> GetAllClients()
        {
            ClientEntity c = null;
            List<ClientEntity> clients = new List<ClientEntity>();

            cmdText = "select * from Customer ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                    clients.Add(c);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return clients;

        }




        public static bool Add(ClientEntity c)
        {
            bool flag = true;
            cmdText = "insert into Customer (Customer_ID,Customer_Name,Customer_Address,Customer_Phone,Customer_Job,Akhesaye,Round)values"+
                "(@Customer_ID,@Customer_Name,@Customer_Address,@Customer_Phone,@Customer_Job,@Akhesaye,@Round)";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_ID", c.Id);
            cmd.Parameters.AddWithValue("@Customer_Name", c.Name);
            cmd.Parameters.AddWithValue("@Customer_Address", c.Address);
            cmd.Parameters.AddWithValue("@Customer_Phone", c.Phone);
            cmd.Parameters.AddWithValue("@Customer_Job", c.Job);
            //cmd.Parameters.AddWithValue("@Customer_DOB", c.DateOfBirth);
            cmd.Parameters.AddWithValue("@Akhesaye", c.Akhesaye);
            cmd.Parameters.AddWithValue("@Round", c.Round);

            try
            {
                con.Open();
                Int32 numOfRow = cmd.ExecuteNonQuery();
                if (numOfRow == 0)
                    flag = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى البيان كود العميل ربما يكون مكرراً" + ex.Message);
                flag = false;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;

        }





        public static bool Update(ClientEntity c)
        {
            Boolean flag = false;
            cmdText = "UPDATE Customer SET Customer_Name=@Customer_Name ,Customer_Address=@Customer_Address,Customer_Phone=@Customer_Phone,Customer_Job=@Customer_Job ,Akhesaye=@Akhesaye ,Round=@Round WHERE Customer_ID=@Customer_ID";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_Name", c.Name);
            cmd.Parameters.AddWithValue("@Customer_Address", c.Address);
            cmd.Parameters.AddWithValue("@Customer_Phone", c.Phone);
            cmd.Parameters.AddWithValue("@Customer_Job", c.Job);
            //cmd.Parameters.AddWithValue("@Customer_DOB", c.DateOfBirth);
            cmd.Parameters.AddWithValue("@Akhesaye", c.Akhesaye);
            cmd.Parameters.AddWithValue("@Round", c.Round);
            cmd.Parameters.AddWithValue("@Customer_ID", c.Id);

            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                // MessageBox.Show(numOfRow + "Record updated");
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }


        /*
           Delete using id or Serial 
        */

        public static bool Delete(Int32 c)
        {
            Boolean flag = false;
            cmdText = "Delete from Customer WHERE Customer_ID=@Custome_Id";


            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Custome_Id", c);
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                //  MessageBox.Show(numOfRow + "Record Deleted");
                flag = true;


            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

            }
            return flag;
        }





        public static List<ClientEntity> GetClients(string name)
        {
            ClientEntity c = null;
            List<ClientEntity> clients = new List<ClientEntity>();

            cmdText = " select * from Customer where Customer_Name LIKE @Customer_Name ";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_Name", name);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                    clients.Add(c);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return clients;
        }











        private static ClientEntity fillEntityFromReader(SqlDataReader reader)
        {
            ClientEntity c = new ClientEntity();
            c.Id = Int32.Parse(reader["Customer_ID"].ToString());
            c.Name = reader["Customer_Name"].ToString();

            if (reader["Customer_Address"] != null)
                c.Address = reader["Customer_Address"].ToString();
            if (reader["Customer_Phone"] != null)
                c.Phone = reader["Customer_Phone"].ToString();
            if (reader["Customer_Job"] != null)
                c.Job = reader["Customer_Job"].ToString();
            //  if (reader["DOB"] != null)
            //    c.DateOfBirth = (DateTime)reader["DOB"];
            if (reader["Akhesaye"] != null)
                c.Akhesaye = reader["Akhesaye"].ToString();
            if (reader["Round"] != null)
                c.Round = Int32.Parse(reader["Round"].ToString());
            
            return c;
        }


    }
}